package com.tcode.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tcode.bean.UserBean;
import com.tcode.utils.JDBCUtil;

public class UserDao {
	
	

	
	public int getUserByUserNameAndPwd(UserBean u) throws Exception{
		int i =0;
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) from sys_user_t where uname = ? and pwd =? ");
		
		
		Connection con = JDBCUtil.getConection();
		
		PreparedStatement ps = con.prepareStatement(sql.toString());
		
		ps.setString(1, u.getName());
		ps.setString(2, u.getPhone());
		
		ResultSet rs = ps.executeQuery();
		
		while(rs.next()){
			i=rs.getInt(1);
		}
		
		
		return  i;
	}
	
	public UserBean getUserbeanByUserNameAndPwd(UserBean u) throws Exception{
		UserBean user=new UserBean();
		StringBuffer sql = new StringBuffer();
		sql.append("select uid,deptid,uname,pwd , DATE_FORMAT(createdate,'%Y-%m-%d %H:%i:%s')"
				+ " AS createdate from sys_user_t where uname = ? and pwd =? ");
		
		
		Connection con = JDBCUtil.getConection();
		
		PreparedStatement ps = con.prepareStatement(sql.toString());
		
		ps.setString(1, u.getName());
		ps.setString(2, u.getPhone());
		
		ResultSet rs = ps.executeQuery();
		System.out.println(rs.next());
		while(rs.next()){
			user.setId(rs.getInt(1));
			user.setDeptId(rs.getInt(2));
			user.setName(rs.getNString("uname"));
			user.setPhone(rs.getNString("pwd"));
			user.setCreatedate(rs.getNString("createdate"));
		}
		
		
		return  user;
	}
	
	public List<UserBean>  getUserList(UserBean u,int indexNo,int pageSize) throws SQLException{
		
		List<UserBean> list = new ArrayList<UserBean>();
		int preNum=0;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT uid,uname,pwd , DATE_FORMAT(createdate,'%Y-%m-%d %H:%i:%s') AS createdate FROM sys_user_t where 1=1 ");
		if(u.getName()!=null&&!("".equals(u.getName()))){
			sql.append(" and uname = ? ");
			preNum++;
		}
		sql.append("order by createdate desc limit ? ,?  ");
		Connection con = JDBCUtil.getConection();
		System.out.println(sql.toString()+","+indexNo+","+pageSize);
		PreparedStatement ps = con.prepareStatement(sql.toString());
		if(preNum!=0){
			ps.setString(1, u.getName());
		}else{
			ps.setInt(1, indexNo);
			ps.setInt(2, pageSize);
		}
		ResultSet rs = ps.executeQuery();
		
		while (rs.next()) {
			UserBean user = new UserBean();
			user.setId(rs.getInt(1));
			user.setName(rs.getString(2));
			user.setPhone(rs.getString(3));
			user.setCreatedate(rs.getString(4));
			list.add(user);
		}
		
		return list;
		
	}
	
	public List<UserBean> searchAllUser() throws SQLException{
		List<UserBean> list = new ArrayList<UserBean>();
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT uid,uname,pwd , DATE_FORMAT(createdate,'%Y-%m-%d %H:%i:%s') AS createdate FROM sys_user_t where 1=1 ");
		
		
		Connection con = JDBCUtil.getConection();
		PreparedStatement ps = con.prepareStatement(sql.toString());
		
		ResultSet rs = ps.executeQuery();
		
		while (rs.next()) {
			UserBean user = new UserBean();
			user.setId(rs.getInt(1));
			user.setName(rs.getString(2));
			user.setPhone(rs.getString(3));
			user.setCreatedate(rs.getString(4));
			list.add(user);
		}
		
		return list;
	}
	
	
	public int getUserCountBySomething(UserBean u) throws Exception{
		
		int i =0;
		int preNum=0;
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) from sys_user_t where 1=1 ");
		if(u.getName()!=null&&!("".equals(u.getName()))){
			sql.append(" and uname = ? ");
			preNum++;
		}
		
		
		Connection con = JDBCUtil.getConection();
		
		PreparedStatement ps = con.prepareStatement(sql.toString());
		if(preNum!=0){
			ps.setString(1, u.getName());
		}
		ResultSet rs = ps.executeQuery();
		
		while(rs.next()){
			i=rs.getInt(1);
		}
		return  i;
	}
	
	
	public int insertUser(UserBean user) throws SQLException{
		int flag = 0;
		
		StringBuffer sql =new StringBuffer();
		
		sql.append("insert into sys_user_t (uname,pwd,deptid) values (?,?,1) ");
		
		Connection con=  JDBCUtil.getConection();
		
		PreparedStatement ps =con.prepareStatement(sql.toString());
		
		ps.setString(1, user.getName());
		ps.setString(2, user.getPhone());
		
		flag = ps.executeUpdate();
		
		return flag;
	}
	
	public int modUser(UserBean user)throws Exception{
		int flag = 0;
		
		StringBuffer sql =new StringBuffer();
		
		sql.append("update sys_user_t set pwd = ? ,deptid=?  where uid = ? ");
		
		Connection con=  JDBCUtil.getConection();
		
		PreparedStatement ps =con.prepareStatement(sql.toString());
		
		ps.setString(1, user.getPhone());
		ps.setInt(2, user.getDeptId());
		ps.setInt(3, user.getId());
		
		flag = ps.executeUpdate();
		
		return flag;
	}

}
